﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class AgressoSQL
    {
        public static string GetSupplierByName()
        {
            return @"
            select * from asuheader where apar_name = @apar_name and status = 'N' and client = 'S1' and apar_gr_id in ('BKS','OWR', 'SBR','CRE') ; 
            ";//filter using client S1 --29042013Carina
        }
         public static string GetSupplierByNameLike(string name)
        {
            return string.Format(@"
            select * from asuheader where apar_id+apar_name+short_name like '%{0}%' and status = 'N'  and client = 'S1'
            ", name);
        }
         public static string GetSupplierByNameLikeClientS1(string name)
         {
             return string.Format(@"
            select * from asuheader where apar_name like '%{0}%' and status = 'N' and client = 'S1'
            ", name);
         }
        public static string GetSupplierByCode()
        {
            return @"
            select * from asuheader where apar_id = @apar_id and status = 'N';
            ";
        }
        public static string GetSupplierByCodeForWinSDS()
        {
            return @"
            select * from asuheader where  apar_id = @apar_id and client='S1' and apar_gr_id = 'CRE' and status = 'N';
            ";
        }
        public static string GetCustomerByName()
        {
            return @"
            select * from acuheader where apar_name = @apar_name and status = 'N' and apar_gr_id in ('CHA','SDE')";
        }
        public static string GetCustomerByCode()
        {
            return @"
            select * from acuheader where apar_id = @apar_id and status = 'N';
            ";
        }
        public static string GetCustomerByNameLikeClientS1(string name)
        {
            return string.Format(@"
            select * from acuheader where apar_name = '%{0}%' and status = 'N' and client = 'S1';", name);
        }
        public static string GetCustomerByNameLike(string name)
        {
            return string.Format(@"
            select * from acuheader where apar_id+apar_name+short_name like '%{0}%' and status = 'N' and client = 'S1'
            ", name);
        }
        public static string GetAglDimValueVesselByDescription()
        {
            return @"
            select * from agldimvalue where attribute_id = 'P5A' and description = @description and status = 'N';
            ";
        }
        public static string GetAglDimValueVesselByDescriptionLike()
        {
            return @"
            select * from agldimvalue where attribute_id = 'P5A' and description like '%'+@vessel+'%' and status = 'N';
            ";
        }
        public static string GetAglDimValueVesselByDescriptionAndClient()
        {
            return @"
            select * from agldimvalue where attribute_id = 'P5A' and description = @description and status = 'N' and client = @client;
            ";
        }
        public static string GetAglDimValueVesselByDescriptionAndClientLike()
        {
            return @"
            select * from agldimvalue where attribute_id = 'P5A' and description like '%' + @vessel + @'%' and status = 'N' and client = @client;
            ";
        }
        public static string GetAglDimValueVesselByDescriptionS1()
        {
            return @"
            select * from agldimvalue where attribute_id = 'P5A' and description = @description and status = 'N' and client = 'S1' ;
            ";
        }
        public static string GetAglDimValueVesselByDescriptionLikeSearch()
        {
            return @"select * from agldimvalue where attribute_id = 'P5A' and description+client+dim_value+rel_value like '%'+@description+'%' and status = 'N'";
        }
        public static string GetAglDimValueProfitCentreByDescriptionListLike()
        {
            return @"select * from agldimvalue where attribute_id = 'A28' and description+client+dim_value+rel_value like '%'+@description+'%' and status = 'N'";
        }
        public static string GetAglDimValueVesselByDimVal()
        {
            return @"
            select * from agldimvalue where attribute_id = 'P5A' and dim_value = @dim_value and status = 'N' ;
            ";
        }

        public static string GetAglDimValueVesselByDimValAndClient()
        {
            return @"select * from agldimvalue where attribute_id = 'P5A' and dim_value = @dim_value and status = 'N' and client=@client";
        }
       
        public static string GetAglDimValueProfitCentreByDescription()
        {
            return @"
            select * from agldimvalue where attribute_id = 'A28' and description = @description and status = 'N' ;
            ";
        }
        public static string GetAglDimValueProfitCentreByDimValue()
        {
            return @"
            select * from agldimvalue where attribute_id = 'A28' and dim_value = @dim_value;";
        }
        public static string GetAglDimValueProfitCentreByDimValueForCrewPayroll()
        {
            return @"
            select * from agldimvalue where attribute_id = 'A28' and dim_value = @dim_value and status = 'N' and client not like 'A%'";
        }
        public static string GetAglDimValueCostCentreByProfitCentreAndVessel()
        {
            return @"
            select 
               c.*
                from agldimvalue v, agldimvalue c, agldimvalue p  where 
                v.attribute_id = 'P5A'
                and v.rel_value = c.dim_value 
                and c.attribute_id = 'C1' 
                and p.dim_value = c.rel_value 
                and p.attribute_id = 'A28'
                and v.client = c.client
                and c.client = p.client
                and p.dim_value = @profitCentre
                and v.dim_value = @vesselDimVal
            ";
        }
        public static string GetAglDimValueCostCentreByProfitCentreDimValAndVesselName()
        {
            return @"
            select 
               c.*
                from agldimvalue v, agldimvalue c, agldimvalue p  where 
                v.attribute_id = 'P5A'
                and v.rel_value = c.dim_value 
                and c.attribute_id = 'C1' 
                and p.dim_value = c.rel_value 
                and p.attribute_id = 'A28'
                and v.client = c.client
                and c.client = p.client
                and p.dim_value = @profitCentre
                and v.description = @vesselNAme
            ";
        }
        public static string GetAglDimValueCostCentreByDimVal()
        {
            return @"
            select *        
                from agldimvalue where 
                attribute_id = 'C1' 
                and dim_value = @dim_value
            ";
        }
        public static string GetAglDimValueLegalEntityByDimVal()
        {
            return @"
            select *        
                from agldimvalue where 
                attribute_id = 'A28' 
                and dim_value = @dim_value
            ";
        }
        public static string GetAglDimValueICVesselByDimVal()
        {
            return @"
            select *        
                from agldimvalue where 
                attribute_id = 'ZZ3' 
                and dim_value = @dim_value
            ";
        }
        public static string GetAglDimValueICVoyageByDimVal()
        {
            return @"
            select *        
                from agldimvalue where 
                attribute_id = 'ZZ4' 
                and dim_value = @dim_value
            ";
        }
        public static string GetAglDimValueLegalEntityByDimValClientS1()
        {
            return @"
            select *        
                from agldimvalue where 
                attribute_id = 'A28' 
                and dim_value = @dim_value
                and client = 'S1'
            ";
        }
        public static string GetPurchaseOrderByExtraOrderRef()
        {
            return @"
            select * from apoheader h where h.ext_ord_ref = @ext_ord_ref and h.status = 'O';
            ";
        }
        public static string GetPurchaseOrderOrdered()
        {
            return @"select * from apoheader where status = 'O' and ext_ord_ref like '%[0-9]%[_]________'";
        }
        public static string GetPurchaseOrderNonOrdered()
        {
            return @"select * from apoheader where status = 'N' and ext_ord_ref like '%[0-9]%[_]________'";
        }
        public static string GetPurchaseOrderAll()
        {
            return @" select h.order_id, h.status, h.ext_ord_ref, d.line_no ,d.sup_article, d.client, d.art_descr
                    from apoheader h, apodetail d where  d.order_id = h.order_id and h.ext_ord_ref like '%[0-9]%[_]________' and h.client = d.client";
        }
        public static string GetPurchaseOrderByExtraOrderRefAndSupArticle()
        {
            return @"
                    select h.order_id, h.ext_ord_ref, d.line_no ,d.sup_article, d.client, d.art_descr
                    from apoheader h, apodetail d where 
                    d.order_id = h.order_id
                    and h.ext_ord_ref = @ext_ord_ref
                    and d.sup_article = @sup_article
            ";
        }

        public static string GetAllPendingGRNArrivedID()
        {
            return "select distinct arrive_id from c_GRN where pickedupflag = 0";
        }
        public static string GetAllGRNByArrivedID()
        {
            return "select * from c_GRN where arrive_id = @arrive_id order by arrive_id, line_no";
        }

        public static string UpdatePickedUpFlagResultByArriveID()
        {
            return "update c_GRN set PickedUpFlag=@PickedUpFlag, ResultDetails=@ResultDetails where arrive_id=@arrive_id";
        }

        public static string GetCountArticleByUOMClient()
        {
           // return @"Select count(*) from algarticle a, algunit u where a.article_id = u.article_id and a.client = u.client
            //    and a.article = @article and u.unit_code=@unit_code and a.client=@client";.
            return @"Select count(*) from algarticle a where a.article = @article and a.client=@client";
        }



        public static string GetWarehouseCount()
        {
            return @"select count(*) from Astwarehouse where dim_5 = @dim_5";
        }
    }
}
